﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace HTComics.Data.Tools
{
    class DataFormatter
    {
        private string _connString;

        public DataFormatter(string connString)
        {
            _connString = connString;
        }

        public DataTable GetNonNumericIssues()
        {
            SqlConnection conn;
            SqlCommand cmd;
            DataTable dt = new DataTable();

            conn = new SqlConnection(_connString);
            conn.Open();
            cmd = conn.CreateCommand();

            try
            {
                cmd.CommandText = "SELECT I.IssueId,I.IssueNumber FROM TBL_Issue I WHERE ISNUMERIC(I.IssueNumber) <> 1";
                SqlDataReader dr = cmd.ExecuteReader();
                dt.Load(dr);
            }
            catch (Exception ex)
            {
            }
            finally
            {
                conn.Close();
            }

            return dt;
        }

        public void FormatIssueNumbers(DataTable dtNonnumericIssues)
        {
            string issue;
            string []issueParts;
            string sql;
            int iIssue;

            SqlConnection conn;
            SqlCommand cmd;

            conn = new SqlConnection(_connString);
            conn.Open();
            
            for (int i = 0; i <= dtNonnumericIssues.Rows.Count - 1; i++)
            {
                issue = dtNonnumericIssues.Rows[i][1].ToString();
                issueParts = issue.Split('-');

                if (Int32.TryParse(issueParts[0], out iIssue))
                {
                    sql = "UPDATE TBL_Issue SET IssueNumber = " + iIssue + ", IssueVariation='" + issueParts[1] + "' WHERE IssueId=" + dtNonnumericIssues.Rows[i][0];
                }
                else
                {
                    sql = "UPDATE TBL_Issue SET IssueNumber = NULL, IssueVariation='" + issue + "' WHERE IssueId=" + dtNonnumericIssues.Rows[i][0];
                }

                try
                {
                    cmd = conn.CreateCommand();
                    cmd.CommandText = sql;                   
                    cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                }
            }

            conn.Close();
        }
    }
}
